package com.cai.dao;

import com.cai.pojo.*;
import com.cai.util.C3p0Util;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;


/**
 * @author RuoLi
 */
public class DbUtil {
    /**
     * 关闭资源
     */
    private static void close(Connection connection, PreparedStatement preparedstatement, ResultSet resultset) {
        try {
            if (connection != null) {
                connection.close();
            }
            if (preparedstatement != null) {
                preparedstatement.close();
            }
            if (resultset != null) {
                resultset.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static int selectCount(String sql) {
        Connection connection = null;
        PreparedStatement preparedstatement = null;
        ResultSet resultset = null;
        int count = -1;
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedstatement = connection.prepareStatement(sql);
            resultset = preparedstatement.executeQuery();
            if (resultset.next()) {
                return 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedstatement, resultset);
        }
        return count;
    }

    /**
     * 查询指定字段 无参数
     *
     * @param sql sql语句
     * @return 查询不到 返回-1 否则返回 1
     */
    public static int selectCountIsExist(String sql) {
        Connection connection = null;
        PreparedStatement preparedstatement = null;
        ResultSet resultset = null;
        int count = -1;
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedstatement = connection.prepareStatement(sql);
            resultset = preparedstatement.executeQuery();
            while (resultset.next()) {
                count = resultset.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedstatement, resultset);
        }
        return count;
    }

    /**
     * 查询指定字段 有参数
     *
     * @param sql    sql语句
     * @param params 参数
     * @return 查询不到 返回-1 否则返回 1
     */
    public static int selectCountIsExist(String sql, String[] params) {
        int count = -1;
        Connection connection = null;
        PreparedStatement preparedstatement = null;
        ResultSet resultset = null;
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedstatement = connection.prepareStatement(sql);
            for (int i = 1; i <= params.length; i++) {
                preparedstatement.setString(i, params[i - 1]);
            }
            resultset = preparedstatement.executeQuery();
            while (resultset.next()) {
                count = resultset.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedstatement, resultset);
        }
        return count;
    }

    /**
     * 查询数据 封装到List<?>集合 有参数
     *
     * @param sql    sql语句
     * @param params 参数
     * @param clazz  实体类
     * @return 返回 List<?> 否则List<?>数据为空
     */
    public List<?> selectContent(String sql, String[] params, Class<?> clazz) {
        QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
        List<?> list = new ArrayList<>();
        try {
            list = runner.query(sql, params, new BeanListHandler<>(clazz));
            System.out.println(list);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询一个字段的值 无参数
     *
     * @param sql sql语句
     * @return 字符串
     */
    public String selectContent(String sql) {
        String param = null;
        Connection connection = null;
        PreparedStatement preparedstatement = null;
        ResultSet resultset = null;
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedstatement = connection.prepareStatement(sql);
            resultset = preparedstatement.executeQuery();
            while (resultset.next()) {
                param = resultset.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedstatement, resultset);
        }
        return param;
    }

    /**
     * 查询一个字段的值 有参数
     *
     * @param sql    sql语句
     * @param params 参数
     * @return String
     */
    public String selectContent(String sql, String[] params) {
        String data = "";
        Connection connection = null;
        PreparedStatement preparedstatement = null;
        ResultSet resultset = null;
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedstatement = connection.prepareStatement(sql);
            for (int i = 1; i < params.length + 1; i++) {
                preparedstatement.setString(i, params[i - 1]);
            }
            resultset = preparedstatement.executeQuery();
            while (resultset.next()) {
                data = resultset.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedstatement, resultset);
        }
        return data;
    }

    /**
     * 更新,删除,插入内容 带参数
     *
     * @param sql    sql语句
     * @param params 参数
     * @return 更新失败返回-1 否则返回1
     */
    public static int updateContent(String sql, Object[] params) {
        QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
        int count = -1;
        try {
            count = runner.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 获取总页数
     *
     * @param pageSize 每页数据量
     * @param sql      调用存储过程
     * @return int 查询不到 return -1
     */
    public static int getTotalPages(String sql, int pageSize) {
        Connection connection = null;
        PreparedStatement preparedstatement = null;
        ResultSet resultset = null;
        String[] params = {String.valueOf(pageSize)};
        int value = -1;
        try {
            connection = C3p0Util.getDataSource().getConnection();
            //所有的 DBMS 提供了一种以标准形式调用已储存过程的方法
            CallableStatement callableStatement = connection.prepareCall(sql);
            callableStatement.setString(1, params[0]);
            //执行
            callableStatement.execute();
            resultset = callableStatement.getResultSet();
            while (resultset.next()) {
                value = Integer.parseInt(resultset.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, null, resultset);
        }
        return value;
    }

    /**
     * 获取分页信息
     *
     * @param sql         调用存储过程
     * @param currentPage 当前页
     * @param pageSize    每页数据量
     * @return list<DetailedTopic>
     */
    public static List<?> getDetailedTopicPageData(String sql, int currentPage, int pageSize) {
        List<DetailedTopic> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedstatement = null;
        ResultSet resultset = null;
        String[] params = {String.valueOf(currentPage), String.valueOf(pageSize)};
        try {
            connection = C3p0Util.getDataSource().getConnection();
            //所有的 DBMS 提供了一种以标准形式调用已储存过程的方法
            CallableStatement callableStatement = connection.prepareCall(sql);
            callableStatement.setString(1, params[0]);
            callableStatement.setString(2, params[1]);
            //执行
            callableStatement.execute();
            resultset = callableStatement.getResultSet();
            //调用结果集
            resultDetailedTopicAdmin(resultset, list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, null, resultset);
        }
        return list;
    }

    public static List<?> getDetailedTopicPageDataUserClient(String sql, int currentPage, int pageSize) {
        List<DetailedTopic> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedstatement = null;
        ResultSet resultset = null;
        String[] params = {String.valueOf(currentPage), String.valueOf(pageSize)};
        try {
            connection = C3p0Util.getDataSource().getConnection();
            //所有的 DBMS 提供了一种以标准形式调用已储存过程的方法
            CallableStatement callableStatement = connection.prepareCall(sql);
            callableStatement.setString(1, params[0]);
            callableStatement.setString(2, params[1]);
            //执行
            callableStatement.execute();
            resultset = callableStatement.getResultSet();
            //调用结果集
            resultDetailedTopicUser(resultset, list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, null, resultset);
        }
        return list;
    }

    public static List<?> getDetailedTopicPageDataChoseChange(String sql, String topicType, int currentPage, int pageSize) {
        Connection connection = null;
        PreparedStatement preparedstatement = null;
        ResultSet resultset = null;
        List<DetailedTopic> list = new ArrayList<>();
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedstatement = connection.prepareStatement(sql);
            preparedstatement.setString(1, topicType);
            preparedstatement.setInt(2, (currentPage - 1) * pageSize);
            preparedstatement.setInt(3, pageSize);
            resultset = preparedstatement.executeQuery();
            resultDetailedTopicChoseChange(resultset, list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedstatement, resultset);
        }
        return list;
    }

    public static List<?> getUserTopicFromDetailedTopicId(String sql) {
        Connection connection = null;
        Statement preparedstatement = null;
        ResultSet resultset = null;
        List<DetailedTopic> list = new ArrayList<>();
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedstatement = connection.createStatement();
//            preparedstatement.setInt(1, detailedTopicId);
            resultset = preparedstatement.executeQuery(sql);
            resultDetailedTopicUser(resultset, list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, null, resultset);
        }
        return list;
    }

    /**
     * @param sql         调用存储过程 参数1 当前页 参数2 数据量
     * @param currentPage 当前页
     * @param pageSize    数据量
     * @return List<DetailedUser>
     */
    public static List<?> getUserInfoPageData(String sql, int currentPage, int pageSize) {
//        String sql = "CALL show_page_tables_DetailedUser(?,?)";
        Connection connection = null;
        PreparedStatement preparedstatement = null;
        ResultSet resultset = null;
        List<DetailedUser> list = new ArrayList<>();
        String[] params = {String.valueOf(currentPage), String.valueOf(pageSize)};
        try {
            //所有的 DBMS 提供了一种以标准形式调用已储存过程的方法
            connection = C3p0Util.getDataSource().getConnection();
            CallableStatement callableStatement = connection.prepareCall(sql);
            callableStatement.setString(1, params[0]);
            callableStatement.setString(2, params[1]);
            //执行
            callableStatement.execute();
            resultset = callableStatement.getResultSet();
            resultDetailedUser(resultset, list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, null, resultset);
        }
        return list;
    }

    /**
     * 根据sql查询单行题目详细信息
     *
     * @param sql    sql语句
     * @param params 参数 如果没有参数，传递String[]类型空数组
     * @return list
     */
    public static List<?> getDetailedTopicData(String sql, String[] params) {
        int length = params.length;
        Connection connection = null;
        PreparedStatement preparedstatement = null;
        ResultSet resultset = null;
        List<DetailedTopic> list = new ArrayList<>();
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedstatement = connection.prepareStatement(sql);
            if (length == 0) {
                //没有参数执行
                resultset = preparedstatement.executeQuery();
                //调用
                resultDetailedTopicAdmin(resultset, list);
            } else {
                for (int i = 0; i < length; i++) {
                    preparedstatement.setString(1, params[i]);
                }
                resultset = preparedstatement.executeQuery();
                //调用
                resultDetailedTopicAdmin(resultset, list);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedstatement, resultset);
        }
        return list;
    }

    /**
     * 获取用户答题界面题目详细信息
     *
     * @return list
     */
    public static List<?> getUserDetailedTopicData(String sql, int detailedTopicId) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultset = null;
        List<UserDetailedTopicData> list = new ArrayList<>();
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, detailedTopicId);
            resultset = preparedStatement.executeQuery();
            //调用方法
            resultUserDetailedTopicData(resultset, list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultset);
        }
        return list;
    }

    public static List<?> getUserRandomDetailedTopicData(String sql) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultset = null;
        List<UserDetailedTopicData> list = new ArrayList<>();
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultset = preparedStatement.executeQuery();
            resultUserDetailedTopicData(resultset, list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultset);
        }
        return list;
    }

    public static List<?> getUserFavoritesTopicInfo(String sql, int userAccount) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultset = null;
        List<DetailedTopic> list = new ArrayList<>();
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, userAccount);
            resultset = preparedStatement.executeQuery();
            resultUserFavoritesTopicInfo(resultset, list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultset);
        }
        return list;
    }

    public static List<?> getUserErrorTopicInfo(String sql, int userAccount) {
        return getUserFavoritesTopicInfo(sql, userAccount);
    }

    /**
     * 获取非重复的知识点
     *
     * @return list
     */
    public static List<?> getTopicKnowledgePointData(String sql) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultset = null;
        List<Topic> list = new ArrayList<>();
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //没有参数执行
            resultset = preparedStatement.executeQuery();
            while (resultset.next()) {
                Topic topic = new Topic();
                topic.setKnowledge_point(resultset.getString("knowledge_point"));
                list.add(topic);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultset);
        }
        return list;
    }

    /**
     * 查询的结果集
     *
     * @param results 结果集
     * @param list    list
     * @throws SQLException
     */
    private static void resultDetailedTopicAdmin(ResultSet results, List<DetailedTopic> list) throws SQLException {
        while (results.next()) {
            DetailedTopic detailedTopic = new DetailedTopic();
            Topic topic = new Topic();
            detailedTopic.setDetailed_topic_id(results.getInt("detailed_topic_id"));
            detailedTopic.setTopic_id(results.getInt("topic_id"));
            detailedTopic.setTopic_title(results.getString("topic_title"));
            detailedTopic.setTopic_check_1(results.getString("topic_check_1"));
            detailedTopic.setTopic_check_2(results.getString("topic_check_2"));
            detailedTopic.setTopic_Check_3(results.getString("topic_check_3"));
            detailedTopic.setTopic_check_4(results.getString("topic_check_4"));
            detailedTopic.setTopic_check_5(results.getString("topic_check_5"));
            detailedTopic.setTopic_answer(results.getString("topic_answer"));
            detailedTopic.setTopic_answer_parse(results.getString("topic_answer_parse"));
            detailedTopic.setFrequency(results.getInt("frequency"));
            topic.setTopic_type(results.getString("topic_type"));
            topic.setGrade(results.getString("grade"));
            topic.setChapter_name(results.getString("chapter_name"));
            topic.setKnowledge_point(results.getString("knowledge_point"));
            topic.setSubject_name(results.getString("subject_name"));
            detailedTopic.setTopic(topic);
            list.add(detailedTopic);
        }
    }

    private static void resultDetailedUser(ResultSet resultSet, List<DetailedUser> list) throws SQLException {
        while (resultSet.next()) {
            DetailedUser detailedUser = new DetailedUser();
            UserLogin userLogin = new UserLogin();
            detailedUser.setUser_account(resultSet.getInt("user_account"));
            detailedUser.setNickname(resultSet.getString("nickname"));
            detailedUser.setRemark(resultSet.getString("remark"));
            detailedUser.setGender(resultSet.getString("gender"));
            detailedUser.setPhone(resultSet.getString("phone"));
            detailedUser.setEmail(resultSet.getString("email"));
            detailedUser.setAddress(resultSet.getString("address"));
            detailedUser.setQuestion_numbers(resultSet.getInt("question_numbers"));
            detailedUser.setQuestion_errors(resultSet.getInt("question_errors"));
            detailedUser.setQuestion_true(resultSet.getInt("question_true"));
            detailedUser.setQuestion_accuracy_rate(resultSet.getFloat("question_accuracy_rate"));
            userLogin.setLogin_id(resultSet.getInt("login_id"));
            userLogin.setLogin_password(resultSet.getString("login_password"));
            userLogin.setLogin_identity(resultSet.getString("login_identity"));
            userLogin.setLogin_status(resultSet.getInt("login_status"));
            detailedUser.setUserLogin(userLogin);
            list.add(detailedUser);
        }
    }

    private static List<DetailedTopic> resultDetailedTopicUser(ResultSet resultSet, List<DetailedTopic> list) throws SQLException {
        while (resultSet.next()) {
            DetailedTopic detailedTopic = new DetailedTopic();
            Topic topic = new Topic();
            detailedTopic.setDetailed_topic_id(resultSet.getInt("detailed_topic_id"));
            detailedTopic.setTopic_title(resultSet.getString("topic_title"));
            detailedTopic.setFrequency(resultSet.getInt("frequency"));
            topic.setKnowledge_point(resultSet.getString("knowledge_point"));
            topic.setTopic_type(resultSet.getString("topic_type"));
            detailedTopic.setTopic(topic);
            list.add(detailedTopic);
        }
        return list;
    }

    private static void resultUserDetailedTopicData(ResultSet resultSet, List<UserDetailedTopicData> list) throws SQLException {
        while (resultSet.next()) {
            UserDetailedTopicData detailedTopicData = new UserDetailedTopicData();
            detailedTopicData.setDetailed_topic_id(resultSet.getInt("detailed_topic_id"));
            detailedTopicData.setTopic_title(resultSet.getString("topic_title"));
            detailedTopicData.setTopic_check_1(resultSet.getString("topic_check_1"));
            detailedTopicData.setTopic_check_2(resultSet.getString("topic_check_2"));
            detailedTopicData.setTopic_check_3(resultSet.getString("topic_check_3"));
            detailedTopicData.setTopic_check_4(resultSet.getString("topic_check_4"));
            detailedTopicData.setTopic_check_5(resultSet.getString("topic_check_5"));
            detailedTopicData.setFrequency(resultSet.getInt("frequency"));
            detailedTopicData.setKnowledge_point(resultSet.getString("knowledge_point"));
            detailedTopicData.setChapter_name(resultSet.getString("chapter_name"));
            detailedTopicData.setGrade(resultSet.getString("grade"));
            detailedTopicData.setTopic_type(resultSet.getString("topic_type"));
            detailedTopicData.setSubject_name(resultSet.getString("subject_name"));
            list.add(detailedTopicData);
        }
    }

    private static void resultUserFavoritesTopicInfo(ResultSet resultset, List<DetailedTopic> list) throws SQLException {
        while (resultset.next()) {
            DetailedTopic detailedTopic = new DetailedTopic();
            Topic topic = new Topic();
            topic.setTopic_type(resultset.getString("topic_type"));
            topic.setKnowledge_point(resultset.getString("knowledge_point"));
            detailedTopic.setDetailed_topic_id(resultset.getInt("detailed_topic_id"));
            detailedTopic.setTopic_title(resultset.getString("topic_title"));
            detailedTopic.setFrequency(resultset.getInt("frequency"));
            detailedTopic.setTopic(topic);
            list.add(detailedTopic);
        }
    }

    private static void resultDetailedTopicChoseChange(ResultSet resultset, List<DetailedTopic> list) throws SQLException {
        resultUserFavoritesTopicInfo(resultset, list);
    }
}
